package jdbc;

import java.sql.*;

public class ConnectDB {
	public static void  main(String []args) {

		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
			String url = "jdbc:sqlserver://119.119.114.88:1433;databaseName=steven2_dsd";
			String user = "sa";
			String password = "P@ssw0rd";

			Connection conn = DriverManager.getConnection(url, user, password);
			Statement stmt = conn.createStatement();
			Statement stmt2 = conn.createStatement();
			int m=0;
			for(int c=1;c<=15;c++){
			String sql2="select distinct kitzone_s from at_ask_bin where  binenable_s='Enable' and kitarea_s like '1055E%' " +
					" order by kitzone_s";
			ResultSet rs2 = stmt.executeQuery(sql2);
			
			
			while (rs2.next()) {
				String zoneName=rs2.getString(1);
				String sql = "select a.kitarea_s ,a.kitzone_s,a.partnumber_s,a.lightaddress_s  from at_ask_bin a " +
				"where  a.binenable_s='Enable' and a.kitzone_s='"+zoneName+"' order by kitarea_s, kitzone_s  " ;
				ResultSet rs = stmt2.executeQuery(sql);
				int i=0;
				while (rs.next()) {
					String areaName=rs.getString(1);
					areaName="GGGGGGGGGGGGGGC00";
					int ss=String.valueOf(c).length();
					areaName=areaName.substring(0,areaName.length()-ss)+c;
					System.out.println(areaName+","+rs.getString(3)+","+((int)(Math.random() *9)+1));
					i++;
					m++;
					if(i>11){
						break;
					}
				}
			}
			}
			System.out.println("*********"+m);
			
//			ResultSet rs = stmt.executeQuery(sql);
//			String area="1055E-KitArea01";
//			String zoneName="1055E-K01Z01";
//			int i=0;
//			while (rs.next()) {
//				//if(area.equals(rs.getString(1))){
//				if(zoneName.equals(rs.getString(2))){
//					int m=rs.getInt(5);
//					
//					if(i<(30/m)){
////						if(zoneName.equals(rs.getString(2))){
////							zoneName=rs.getString(2);
////							int n=30/m;
////							for(int h=0;h<n;h++){
////								String areaName=rs.getString(1);
////								areaName="GGGGGGGGGGGGGG0"+areaName.substring(13,15);
////								System.out.println(zoneName+"--"+areaName+","+rs.getString(3)+","+((int)(Math.random() *10)+1));
////								rs.next();
////								i++;
////							}
////						}else{
////							zoneName=rs.getString(2);
////						}
//						String areaName=rs.getString(1);
//						areaName="GGGGGGGGGGGGGG0"+areaName.substring(13,15);
//						System.out.println(areaName+","+rs.getString(3)+","+((int)(Math.random() *10)+1));
//					}else{
//						
//					}
//					i++;
//					
//				}else{
//					i=0;
//				}
//				//area=rs.getString(1);
//				zoneName=rs.getString(2);
////				System.out.println(rs.getString(2) + "\t");
//			}

			stmt.close();
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			
		}

	}
}
